package com.fuelstations.coderone;

import com.fuelstations.coderone.DatabaseMetadata.*;

import android.database.sqlite.SQLiteDatabase;

//import epl603.project.DatabaseMetadata.ActivityDataTableMetadata;
//import DatabaseMetadata.ActivityHistoryTableMetadata;
//import DatabaseMetadata.AdviceTableMetadata;
//import DatabaseMetadata.BMIResultsTableMetadata;
//import DatabaseMetadata.BMITableMetadata;
//import DatabaseMetadata.AdviceTableMetadata;
//import DatabaseMetadata.MealTableMetadata;
//import DatabaseMetadata.ProductTableMetadata;
//import DatabaseMetadata.ProfileTableMetadata;
//import DatabaseMetadata.RouteTableMetadata;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
import java.util.Vector;

import android.Manifest.permission;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;

public class DatabaseHelper {

	public static final String DATABASE_NAME = "fuelstations.db";
	public static final int DATABASE_VERSION = 6;

	private final DatabaseOpenHelper databaseOpenHelper;
	private SQLiteDatabase sqLiteDatabase = null;

	private static Map<Context, DatabaseHelper> databaseHelpers = new HashMap<Context, DatabaseHelper>();

	static DatabaseHelper getDatabaseHelper(final Context context) {
		if (!databaseHelpers.containsKey(context)) {
			databaseHelpers.put(context, new DatabaseHelper(context));
		}
		return databaseHelpers.get(context);
	}

	private DatabaseHelper(final Context context) {
		this.databaseOpenHelper = new DatabaseOpenHelper(context, DATABASE_NAME, DATABASE_VERSION);
	}

	private SQLiteDatabase getDatabase() {
		if (sqLiteDatabase == null) {
			sqLiteDatabase = databaseOpenHelper.getWritableDatabase();
		}
		return sqLiteDatabase;
	}

	// Function that performs database cleanup
	void cleanup() {
		if (this.sqLiteDatabase != null) {
			this.sqLiteDatabase.close();
			this.sqLiteDatabase = null;
		}
	}

	// Function that deletes all the database tables
	void deleteTables() {
		this.sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + StationTableMetadata.TABLE_NAME);

	}

	public void insertStation(final Station station) {
		this.insertStation(station.getCompany(), station.getOwner(), station.getCity(), station.getAddress(), station.getDistrict(), station.getPostalcode(), station.getUnld95(), station.getUnld98(),
				station.getUnld100(), station.getDiesel(), station.getDieselheat(), station.getKerosene(), station.getOwner_en(), station.getCity_en(), station.getAddress_en(),
				station.getDistrict_en(), station.getLat(), station.getLng());
	}

	// Insert a profile into the database
	public int insertStation(String company, String owner, String city, String address, String district, int postalcode, float unld95, float unld98, float unld100, float diesel, float dieselheat,
			float kerosene, String owner_en, String city_en, String address_en, String district_en, double lat, double lng) {
		ContentValues contentValues = new ContentValues();
		// contentValues.put(ProfileTableMetadata.PROFILE_ID, profile_id);
		contentValues.put(StationTableMetadata.COMPANY, company);
		contentValues.put(StationTableMetadata.OWNER, owner);
		contentValues.put(StationTableMetadata.CITY, city);
		contentValues.put(StationTableMetadata.ADDRESS, address);
		contentValues.put(StationTableMetadata.DISTRICT, district);
		contentValues.put(StationTableMetadata.POSTALCODE, postalcode);
		contentValues.put(StationTableMetadata.UNLD95, unld95);
		contentValues.put(StationTableMetadata.UNLD98, unld98);
		contentValues.put(StationTableMetadata.UNLD100, unld100);
		contentValues.put(StationTableMetadata.DIESEL, diesel);
		contentValues.put(StationTableMetadata.DIESEL_HEATING, dieselheat);
		contentValues.put(StationTableMetadata.KEROSENE, kerosene);
		contentValues.put(StationTableMetadata.OWNER_EN, owner_en);
		contentValues.put(StationTableMetadata.CITY_EN, city_en);
		contentValues.put(StationTableMetadata.ADDRESS_EN, address_en);
		contentValues.put(StationTableMetadata.DISTRICT_EN, district_en);
		contentValues.put(StationTableMetadata.LAT, lat);
		contentValues.put(StationTableMetadata.LNG, lng);

		getDatabase().insert(StationTableMetadata.TABLE_NAME, null, contentValues);

		String query = "SELECT " + StationTableMetadata.STATION_ID + " FROM " + StationTableMetadata.TABLE_NAME + " order by " + StationTableMetadata.STATION_ID + " DESC limit 1";
		Cursor c = null;
		try {
			c = getDatabase().rawQuery(query, null);
		} catch (Exception e) {
			System.out.print("");
		}

		long lastId = 0;
		if (c != null && c.moveToFirst()) {

			lastId = c.getLong(0); // The 0 is the column index, we only have 1
									// column, so the index is 0
		}
		c.close();
		return (int) lastId;
	}

	// Delete a profile entry, given its Id
	public void deleteStationById(final int id) {
		getDatabase().delete(StationTableMetadata.TABLE_NAME, StationTableMetadata.STATION_ID + "= '" + id + "'", null);
	}

	// Return all the profile entries

	// public Vector<Station> getAllStations() {
	public ArrayList<HashMap<String, String>> getAllStations(String strCity, String strDistrict, String strFuelType) {
		ArrayList<HashMap<String, String>> stationsList = new ArrayList<HashMap<String, String>>();
		HashMap<String, String> map = null;

		if (strCity == null) {

			if (Strings.getLang().equals("el")) {
				strCity = StationTableMetadata.CITY;
			} else if (Strings.getLang().equals("en")) {
				strCity = StationTableMetadata.CITY_EN;
			}
		} else {
			strCity = "'" + strCity + "'";
		}

		if (strDistrict == null) {
			if (Strings.getLang().equals("el")) {
				strDistrict = StationTableMetadata.DISTRICT;
			} else if (Strings.getLang().equals("en")) {
				strDistrict = StationTableMetadata.DISTRICT_EN;
			}
			
		} else {
			strDistrict = "'" + strDistrict + "'";
		}

		if (strFuelType == null) {
			strFuelType = StationTableMetadata.UNLD95;
		}

		String distr_lang = null;
		if (Strings.getLang().equals("el")) {
			distr_lang = StationTableMetadata.DISTRICT;
		} else if (Strings.getLang().equals("en")) {
			distr_lang = StationTableMetadata.DISTRICT_EN;
		}

		String city_lang = null;
		if (Strings.getLang().equals("el")) {
			city_lang = StationTableMetadata.CITY;
		} else if (Strings.getLang().equals("en")) {
			city_lang = StationTableMetadata.CITY_EN;
		}
		
		// final Vector<Station> allStations = new Vector<Station>();
		final Cursor cursor = getDatabase().query(StationTableMetadata.TABLE_NAME // from
				, StationTableMetadata.ALL_COLUMNS // select
				, city_lang + " = " + strCity + " and " + distr_lang + " = " + strDistrict + " and " + strFuelType + " <> 0 "// where
				, null // args
				, null // group by
				, null // having
				, strFuelType + " ASC;" // order by
		);

		// final int ID_COLUMN_INDEX =
		// cursor.getColumnIndex(StationTableMetadata.STATION_ID);
		final int COMPANY_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.COMPANY);
		final int OWNER_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.OWNER);
		final int CITY_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.CITY);
		final int ADDRESS_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.ADDRESS);
		final int DISTRICT_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.DISTRICT);
		final int POSTALCODE_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.POSTALCODE);
		final int UNLD95_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.UNLD95);
		final int UNLD98_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.UNLD98);
		final int UNLD100_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.UNLD100);
		final int DIESEL_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.DIESEL);
		final int DIESEL_HEATING_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.DIESEL_HEATING);
		final int KEROSENE_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.KEROSENE);
		final int OWNER_EN_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.OWNER_EN);
		final int CITY_EN_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.CITY_EN);
		final int ADDRESS_EN_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.ADDRESS_EN);
		final int DISTRICT_EN_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.DISTRICT_EN);
		final int LAT_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.LAT);
		final int LNG_COLUMN_INDEX = cursor.getColumnIndex(StationTableMetadata.LNG);

		cursor.moveToFirst();
		final int numOfRecords = cursor.getCount();

		for (int i = 0; i < numOfRecords; i++) {
			// final int id = cursor.getInt(ID_COLUMN_INDEX);
			final String company = cursor.getString(COMPANY_COLUMN_INDEX);
			final String owner = cursor.getString(OWNER_COLUMN_INDEX);
			final String city = cursor.getString(CITY_COLUMN_INDEX);
			final String address = cursor.getString(ADDRESS_COLUMN_INDEX);
			final String district = cursor.getString(DISTRICT_COLUMN_INDEX);
			final int postalcode = cursor.getInt(POSTALCODE_COLUMN_INDEX);
			final float unld95 = cursor.getFloat(UNLD95_COLUMN_INDEX);
			final float unld98 = cursor.getFloat(UNLD98_COLUMN_INDEX);
			final float unld100 = cursor.getFloat(UNLD100_COLUMN_INDEX);
			final float diesel = cursor.getFloat(DIESEL_COLUMN_INDEX);
			final float dieselheat = cursor.getFloat(DIESEL_HEATING_COLUMN_INDEX);
			final float kerosene = cursor.getFloat(KEROSENE_COLUMN_INDEX);
			final String owner_en = cursor.getString(OWNER_EN_COLUMN_INDEX);
			final String city_en = cursor.getString(CITY_EN_COLUMN_INDEX);
			final String address_en = cursor.getString(ADDRESS_EN_COLUMN_INDEX);
			final String district_en = cursor.getString(DISTRICT_EN_COLUMN_INDEX);
			final double lat = cursor.getDouble(LAT_COLUMN_INDEX);
			final double lng = cursor.getDouble(LNG_COLUMN_INDEX);

			map = new HashMap<String, String>();
			map.put(HashTags.KEY_COMPANY, company);
			map.put(HashTags.KEY_OWNER, owner);
			map.put(HashTags.KEY_CITY, city);
			map.put(HashTags.KEY_ADDRESS, address);
			map.put(HashTags.KEY_DISTRICT, district);
			map.put(HashTags.KEY_POSTAL_CODE, Integer.toString(postalcode));
			map.put(HashTags.KEY_UNLD95, Float.toString(unld95));
			map.put(HashTags.KEY_UNLD98, Float.toString(unld98));
			map.put(HashTags.KEY_UNLD100, Float.toString(unld100));
			map.put(HashTags.KEY_DIESEL, Float.toString(diesel));
			map.put(HashTags.KEY_DIESEL_HEAT, Float.toString(dieselheat));
			map.put(HashTags.KEY_KEROSENE, Float.toString(kerosene));
			map.put(HashTags.KEY_OWNER_EN, owner_en);
			map.put(HashTags.KEY_CITY_EN, city_en);
			map.put(HashTags.KEY_ADDRESS_EN, address_en);
			map.put(HashTags.KEY_DISTRICT_EN, district_en);
			map.put(HashTags.KEY_LAT, Double.toString(lat));
			map.put(HashTags.KEY_LNG, Double.toString(lng));

			stationsList.add(map);

			cursor.moveToNext();

		}
		cursor.close();
		// return allStations;
		return stationsList;

	}

	public ArrayList<String> getAllCities() {
		ArrayList<String> citiesList = new ArrayList<String>();

		String city_lang = null;
		if (Strings.getLang().equals("el")) {
			city_lang = StationTableMetadata.CITY;
		} else if (Strings.getLang().equals("en")) {
			city_lang = StationTableMetadata.CITY_EN;
		}

		String sqlStr = "select distinct " + city_lang + " from " + StationTableMetadata.TABLE_NAME + " order by " + city_lang + " ASC;";
		final Cursor cursor = getDatabase().rawQuery(sqlStr, null);

		final int CITY_COLUMN_INDEX = cursor.getColumnIndex(city_lang);

		// final int CITY_EN_COLUMN_INDEX =
		// cursor.getColumnIndex(StationTableMetadata.CITY_EN);

		cursor.moveToFirst();
		final int numOfRecords = cursor.getCount();

		for (int i = 0; i < numOfRecords; i++) {

			final String city = cursor.getString(CITY_COLUMN_INDEX);

			citiesList.add(city);

			cursor.moveToNext();

		}
		cursor.close();
		// return allStations;
		return citiesList;
	}

	public ArrayList<String> getDistrictOfCities(String strCity) {
		ArrayList<String> districtList = new ArrayList<String>();

		String distr_lang = null;
		if (Strings.getLang().equals("el")) {
			distr_lang = StationTableMetadata.DISTRICT;
		} else if (Strings.getLang().equals("en")) {
			distr_lang = StationTableMetadata.DISTRICT_EN;
		}

		String city_lang = null;
		if (Strings.getLang().equals("el")) {
			city_lang = StationTableMetadata.CITY;
		} else if (Strings.getLang().equals("en")) {
			city_lang = StationTableMetadata.CITY_EN;
		}

		String sqlStr = "select " + Strings.districtAll + " as " + distr_lang + " union select " + distr_lang + " from " + StationTableMetadata.TABLE_NAME + " where " + city_lang + " ='" + strCity
				+ "'" + " order by " + distr_lang + " COLLATE NOCASE ASC";
		final Cursor cursor = getDatabase().rawQuery(sqlStr, null);

		final int DISTRICT_COLUMN_INDEX = cursor.getColumnIndex(distr_lang);

		// final int CITY_EN_COLUMN_INDEX =
		// cursor.getColumnIndex(StationTableMetadata.CITY_EN);

		cursor.moveToFirst();
		final int numOfRecords = cursor.getCount();

		for (int i = 0; i < numOfRecords; i++) {

			final String district = cursor.getString(DISTRICT_COLUMN_INDEX);

			// final String city_en = cursor.getString(CITY_EN_COLUMN_INDEX);

			districtList.add(district);

			cursor.moveToNext();

		}
		cursor.close();
		// return allStations;
		return districtList;
	}

	/*
	 * final Cursor cursor = getDatabase().query(
	 * StationTableMetadata.TABLE_NAME, StationTableMetadata.ALL_COLUMNS,
	 * StationTableMetadata.PROFILE_ID + "='" + idQuery + "'", null, null, null,
	 * StationTableMetadata.DEFAULT_SORT_ORDER);
	 */

	public int deleteAllStations() {
		return getDatabase().delete(StationTableMetadata.TABLE_NAME, "1", null);
	}

}